package com.soft.jdbc.student.dao;

import com.soft.jdbc.student.entity.Student;
import com.soft.jdbc.student.utils.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @author yzh
 * @desc TODO
 * @date 2025/6/6
 */
public class StudentDAO {
    // 统计学生人数
    public int countStudents() {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int count = 0;

        try {
            conn = DBUtil.getConn();
            String sql = "select count(*) from stus";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn);
        }
        return count;
    }

    // 获取所有学生列表
    public List<Student> getAllStudents() {
        List<Student> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConn();
            String sql = "select * from stus";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            while (rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String gender = rs.getString("gender");
                String grade = rs.getString("grade");
                String phone = rs.getString("phone");
                String email = rs.getString("email");
                Date birthday = rs.getDate("birthday");
                list.add(new Student(id, name, gender, grade, phone, email, birthday));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn);
        }
        return list;
    }

    // 按学号查询学生
    public Student getStudentById(String id) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Student student = null;

        try {
            conn = DBUtil.getConn();
            String sql = "select * from stus where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, id);
            rs = ps.executeQuery();

            while (rs.next()) {
                student = new Student(
                        rs.getString("id"),
                        rs.getString("name"),
                        rs.getString("gender"),
                        rs.getString("grade"),
                        rs.getString("phone"),
                        rs.getString("email"),
                        rs.getDate("birthday")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn);
        }
        return student;
    }

    // 按姓名查询学生
    public List<Student> getStudentsByName(String name) {
        List<Student> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConn();
            String sql = "select * from stus where name like ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, "%" + name + "%");
            rs = ps.executeQuery();

            while (rs.next()) {
                Student student = new Student(
                        rs.getString("id"),
                        rs.getString("name"),
                        rs.getString("gender"),
                        rs.getString("grade"),
                        rs.getString("phone"),
                        rs.getString("email"),
                        rs.getDate("birthday")
                );
                list.add(student);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn);
        }
        return list;
    }

    // 修改学生出生日期
    public boolean updateStudentBirthday(String id, Date newBirthday) {
        Connection conn = null;
        PreparedStatement ps = null;
        boolean success = false;

        try {
            conn = DBUtil.getConn();
            String sql = "update stus set birthday = ? where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setDate(1, newBirthday);
            ps.setString(2, id);
            int rows = ps.executeUpdate();
            success = rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn);
        }
        return success;
    }

    // 删除学生记录
    public boolean deleteStudent(String id) {
        Connection conn = null;
        PreparedStatement ps = null;
        boolean success = false;

        try {
            conn = DBUtil.getConn();
            String sql = "delete from stus where id = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, id);
            int rows = ps.executeUpdate();
            success = rows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(conn);
        }
        return success;
    }
}
